package com.tom.springboot3demo;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.util.HashMap;
import java.util.Map;

/**
 * poi execel 有单元格合并如何导入到数据库中
 */
public class ExcelToDatabaseWithMergedCells {
    private static final String EXCEL_FILE_PATH = "C:\\Users\\86188\\Desktop\\springboot3.x-technology-tools\\springboot3-demo\\src\\test\\resources\\20240927.xlsx";


    public static void main(String[] args) throws Exception{

            FileInputStream fis = new FileInputStream(EXCEL_FILE_PATH);
            Workbook workbook = new XSSFWorkbook(fis);
            Sheet sheet = workbook.getSheetAt(0); // 获取第一个工作表
            Map<String, String> mergedCellsMap = getMergedCellsMap(sheet);
            System.out.println(mergedCellsMap);


    }

    private static Map<String, String> getMergedCellsMap(Sheet sheet) {
        Map<String, String> mergedCellsMap = new HashMap<>();
        //for (int i = 1; i < sheet.getNumMergedRegions(); i++) {
        for (int i = 1; i < 2; i++) {
            CellRangeAddress region = sheet.getMergedRegion(i);
            int firstRow = region.getFirstRow();
            int lastRow = region.getLastRow();
            int firstCol = region.getFirstColumn();
            int lastCol = region.getLastColumn();

            Row row = sheet.getRow(firstRow);
            if (row != null) {
                Cell cell = row.getCell(firstCol);
                if (cell != null) {
                    String value = getCellValue(cell);
                    for (int r = firstRow; r <= lastRow; r++) {
                        for (int c = firstCol; c <= lastCol; c++) {
                            mergedCellsMap.put(r + "-" + c, value);
                        }
                    }
                }
            }
        }
        return mergedCellsMap;
    }

    private static String getCellValue(Cell cell) {
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf(cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }
}
